// 从award_info.db中加载并读写用户指定获奖数据用的类

// 关闭VSCode非必要报错
// ignore_for_file: unnecessary_import, camel_case_types, non_constant_identifier_names, unnecessary_brace_in_string_interps, unnecessary_string_interpolations, constant_identifier_names, await_only_futures

import 'dart:io';
import 'package:flutter/material.dart';
import 'package:path/path.dart';     // 拼接路径用扩展
import 'package:project_award_statistics/utils/load_award_info_from_db/award_model.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';     // 导入sqlite3数据库(Windows和Linux)用扩展
import 'package:sqflite/sqflite.dart';     // 导入sqlite3数据库(Android)用扩展及总线

class DBProvider_AwardInfo extends ChangeNotifier{

  initDB(String stu_name) async // initDB函数用于返回一个数据库对象
    {
      // 检测到桌面端平台则进行sqflite插件的ffi子插件加载操作
      if (Platform.isWindows || Platform.isLinux || Platform.isMacOS) 
        {
          sqfliteFfiInit();
          databaseFactory = databaseFactoryFfi;
        }

      // 异步打开/新建award_info数据库文件到应该存储数据库的地方并作为对象返回
      return openDatabase(
        join(await getDatabasesPath(), 'award_info.db'),
        onCreate: (db, version) async {
          await db.execute('CREATE TABLE ${stu_name} (award_time TEXT,award_name TEXT,region_level TEXT,award_level TEXT)');
        },
        version: 1,
      );
    }

  // 检查对应学生的表是否存在的函数,下方get_award_list方法会用到
  Future<bool> doesTableExist(Database db, String stu_name) async  
    {
      var res = await db.rawQuery('SELECT name FROM sqlite_master WHERE type=\'table\' AND name=\'${stu_name}\'');
      return res.isNotEmpty;
    }

  Future <List<award_model>> get_award_list (String stu_name) async 
    { 
      Database database = await initDB(stu_name);
      bool if_stu_exist=await doesTableExist(database, stu_name);
      if (!if_stu_exist)    // 检查如果对应学生的表不存在,那么就新建一张
        {
          await database.execute('CREATE TABLE ${stu_name} (award_time TEXT,award_name TEXT,region_level TEXT,award_level TEXT)');    // 新建对应学生信息的表
        }
      final List<Map<String, Object?>> awardMaps = await database.query("${stu_name}");   // 从stu_name表名中提取出获奖数据对象
      return await [
        for (final {'award_time': award_time as String, 'award_name': award_name as String, 'region_level': region_level as String,'award_level':award_level as String} in awardMaps)
          award_model(award_time: award_time, award_name: award_name, region_level: region_level,award_level: award_level),
      ];                    
    }

  // 该函数用于插入新的获奖数据
  Future <void> insert_new_award (award_model new_award_info,String stu_name) async 
    {
      Database database = await initDB(stu_name);   // 等待异步执行完成
      //final insert_map=new_award_info.info_to_map();
      await database.execute("INSERT INTO ${stu_name} (award_time,award_name,region_level,award_level) VALUES ('${new_award_info.award_time}','${new_award_info.award_name}','${new_award_info.region_level}','${new_award_info.award_level}')");        
      return;
    }

  // 该函数用于从特定表中删除指定时间与获奖内容的数据行
  Future <void> delete_award_info (String stu_name,String award_name,String award_time) async
    {
      Database database = await initDB(stu_name);   // 等待异步加载完数据库
      await database.execute("DELETE FROM ${stu_name} WHERE award_name = '${award_name}' AND award_time = '${award_time}'");     //执行SQL语句进行删除
    }  
}
